import os
from datetime import date
from openpyxl import Workbook
from typing import List, Optional
from decimal import Decimal
import json
from datetime import datetime
from dateutil.relativedelta import relativedelta
from dataclasses import dataclass
from pathlib import Path

# 假设这些类已经在其他文件中定义
from report.parser.EmCreditPositionParser import EmCreditPositionParser, EmCreditPosition
from report.parser.EmNormalPositionParser import EmNormalPositionParser
from report.parser.FutuAccountCsvParser import FutuAccountCsvParser
from report.parser.FutuStockPositionParser import FutuStockPositionParser
from report.parser.WeekReportExtInfoParser import WeekReportExtInfoParser
from report.fetch_weekly_line import get_stock_weekly_data


class WeeklyReportExcelGenerator:
    def __init__(self):
        # 周度报告扩展信息
        self.ext_info = None
        # 富途账户信息
        self.futu_account_info = None
        # 富途持仓信息
        self.futu_stock_position_list = None
        # 东方财富普通账户持仓信息
        self.em_normal_position = None
        # 东方财富信用账户持仓信息
        self.em_credit_position = None
        # 东方财富期权账户持仓信息
        self.em_option_position = None
        # 报告日期
        self.report_date = None

        # 初始化解析器
        self.futu_account_csv_parser = FutuAccountCsvParser()
        self.futu_stock_position_parser = FutuStockPositionParser()
        self.em_normal_position_parser = EmNormalPositionParser()
        self.em_credit_position_parser = EmCreditPositionParser()
        # self.em_option_csv_parser = EmOptionCsvParser()  # 假设后续实现
        self.week_report_ext_info_parser = WeekReportExtInfoParser()

    def do_report(self,shift: int = 0):
        """
        执行报告生成操作
        """
        # 假设 WeekReporterFilePicker 类已经实现
        from week_reporter_file_picker import WeekReporterFilePicker
        picker = WeekReporterFilePicker()
        file_info = picker.pick(shift = shift)
        file_info.check()
        print(f"fileInfo: {file_info}")

        try:
            # 这里假设解析方法的参数是文件路径
            self.futu_account_info = self.futu_account_csv_parser.parse(file_info.this_week_futu_account)
            self.futu_stock_position_list = self.futu_stock_position_parser.parse(file_info.this_week_futu_position)
            self.em_normal_position = self.em_normal_position_parser.parse(file_info.this_week_em_normal)
            
            # 检查 this_week_em_credit 是否为空，如果为空则创建新的 EmCreditPosition 对象
            if file_info.this_week_em_credit is not None:
                self.em_credit_position = self.em_credit_position_parser.parse(file_info.this_week_em_credit)
            else:
                self.em_credit_position = EmCreditPosition()
            
            # em_option_position_list = self.em_option_csv_parser.parse(file_info.this_week_em_option)
            # assert len(em_option_position_list) == 1
            # self.em_option_position = em_option_position_list[0]
            self.ext_info = self.week_report_ext_info_parser.parse(file_info.this_week_ext)

            self.report_date = date.today()
            formatted_date = self.report_date.strftime('%Y%m%d')
            file_path = os.path.join('D:\\work\\other_side_happiness\\report', f'{formatted_date}_gen_py.xls')
            if os.path.exists(file_path):
                os.remove(file_path)
            print(f"excel: {file_path}")
            self.generate_excel(file_path)

        except Exception as e:
            e.printStackTrace()

    def generate_excel(self, file_path: str):
        """
        生成Excel报告
        :param file_path: Excel文件路径
        """
        workbook = Workbook()
        try:
            # 删除默认创建的Sheet
            if 'Sheet' in workbook.sheetnames:
                workbook.remove(workbook['Sheet'])
            # 创建所有工作表
            self.create_futu_hk_sheet(workbook)
            self.create_em_stock_detail_sheet(workbook)
            self.create_em_summary_sheet(workbook)
            self.create_fund_summary_sheet(workbook)
            self.create_baseline_sheet(workbook)
            self.create_debt_ratio_sheet(workbook)

            # 写入文件
            workbook.save(file_path)
        except Exception as e:
            e.printStackTrace()
        finally:
            workbook.close()

    def create_futu_hk_sheet(self, workbook: Workbook):
        """
        创建港股周报工作表
        :param workbook: Excel工作簿
        """
        sheet = workbook.create_sheet("港股周报")

        futu_item_config = load_report_item_config("config/report_futu_item_config.json")

        # key:item.name,value：item 最近一周涨幅
        weekly_change_dict = self.fetch_item_weekly_last_change(futu_item_config)

        # 创建标题行
        header_row = [
            "当前日期", "富途证券总值(HKD)", "净资产", "港币现金", "在途资产", "check",
            "本周收益", "本年收益", "本周收益率", "本年收益率", "累计收益",
            "杠杆倍数", "汇率"
        ]
        for item in futu_item_config:
            name = item['name']
            header_row.append(name)
            header_row.append("周涨幅")
        sheet.append(header_row)

        # 创建数据行
        data_values = [
            self.report_date.strftime('%Y-%m-%d'),
            self.futu_account_info.long_market_value,
            self.futu_account_info.net_asset,
            self.futu_account_info.net_asset - self.futu_account_info.long_market_value - self.futu_account_info.in_transit_assets,
            self.futu_account_info.in_transit_assets,
            0,  # check
            0,  # 本周收益 todo
            0,  # self.futu_account_info.net_asset - self.ext_info.futu_initial_assets,
            0,  # 本周收益率 todo
            0, # 本年收益率 #(self.futu_account_info.net_asset - self.ext_info.futu_initial_assets) / self.ext_info.futu_initial_assets,
            0,  # 本年收益率 self.futu_account_info.net_asset - Decimal(613927.91) - Decimal(160000),
            0,  # round(self.futu_account_info.long_market_value / self.futu_account_info.net_asset, 2),
            self.ext_info.hkd_to_cny_rate
        ]

        # 动态添加股票市值和涨跌幅数据
        for item in futu_item_config:
            ts_code = item['tsCode'].split('.')[0]
            # 添加市值数据
            data_values.append(self.find_futu_stock_market_value(ts_code))
            # 添加涨跌幅数据
            data_values.append(weekly_change_dict.get(item['name'], ""))  # 使用get方法避免KeyError

        # 将数据行添加到sheet
        sheet.append(data_values)

    def fetch_item_weekly_last_change(self, futu_item_config):
        '''
        根据配置的 item 查询最近一周的周涨幅
        :param futu_item_config:
        :return:  结果字典 key:item.name,value：item 最近一周涨幅
        '''

        # 结果字典 key:item.name,value：item 最近一周涨幅
        weekly_change_dict = {}

        # 获取当前日期
        current_date = datetime.now()
        # 计算一个月前的日期
        one_month_ago = current_date - relativedelta(months=2)
        # 格式化为yyyyMMdd
        start_date = one_month_ago.strftime("%Y%m%d")
        end_date = current_date.strftime("%Y%m%d")
        # 遍历获取每个证券的周线数据
        for item in futu_item_config:
            name = item['name']
            ts_code = item['tsCode']
            item_type = item['itemType']
            print(f"\n正在获取{name}周线数据...")
            df = get_stock_weekly_data(item, start_date=start_date, end_date=end_date)
            # print(df)
            if df is not None and not df.empty:
                last_change = df.sort_values('日期').iloc[-1]['涨跌幅']
                weekly_change_dict[name] = last_change
            else:
                print(f"{name}数据获取失败或返回空数据")
        return weekly_change_dict

    def find_futu_stock_market_value(self, stock_code: str) -> float:
        """
        查找特定股票市值
        :param stock_code: 股票代码
        :return: 股票市值
        """
        for position in self.futu_stock_position_list:
            if position.code == stock_code:
                return position.market_value
        return 0

    def create_em_stock_detail_sheet(self, workbook: Workbook):
        """
        创建东方财富个股持仓明细工作表
        :param workbook: Excel工作簿
        """
        sheet = workbook.create_sheet("东方财富个股持仓明细")

        em_item_config = load_report_item_config("config/report_em_item_config.json")
        # 拉周线 key:item.name,value：item 最近一周涨幅
        weekly_change_dict = self.fetch_item_weekly_last_change(em_item_config)

        # 创建标题行
        header_row = ["当前日期"]
        for item in em_item_config:
            name = item['name']
            account_list = item["accountList"]
            for account in account_list:
                header_row.append(f"{name}{account}市值")
            if len(account_list) >= 2:
                header_row.append(f"{name}总市值")
            header_row.append("周涨幅")
        sheet.append(header_row)

        # header_row = sheet.append([
        #     "当前日期", "招商银行普通市值", "招商银行信用市值", "招商银行总市值", "",
        #     "300ETF普通市值", "", "比亚迪普通市值", "", "电池etf普通市值", "",
        #     "央企红利普通市值", "", "宁德时代普通市值", "宁德时代信用市值", "宁德时代总市值", "",
        #     "美的普通市值", "", "军工etf普通市值", "", "创新药普通市值", "",
        #     "正泰电器普通市值", "", "韦尔股份普通市值", "", "证券etf普通市值", ""
        # ])
        # 获取当前日期
        current_date = datetime.now()
        # 计算一个月前的日期
        one_month_ago = current_date - relativedelta(months=2)
        # 格式化为yyyyMMdd
        start_date = one_month_ago.strftime("%Y%m%d")
        end_date = current_date.strftime("%Y%m%d")

        # 创建数据行
        data_row = [self.report_date.strftime('%Y-%m-%d')]
        for item in em_item_config:
            ts_code = item['tsCode'].split('.')[0]
            name = item['name']
            account_list = item["accountList"]
            for account in account_list:
                if account == 'NORMAL':
                    data_row.append(self.get_stock_market_value(self.em_normal_position.positions, ts_code))
                elif account == 'CREDIT':
                    data_row.append(self.get_credit_stock_market_value(self.em_credit_position.position_items, ts_code))
                else:
                    raise ValueError(f"unknown type:{account}")
            if len(account_list) >= 2:
                total = self.get_stock_market_value(self.em_normal_position.positions,
                                                    ts_code) + self.get_credit_stock_market_value(
                    self.em_credit_position.position_items, ts_code)
                data_row.append(total)

                # 添加涨跌幅数据
            data_row.append(weekly_change_dict.get(item['name'], ""))
        sheet.append(data_row)



        # data_row = sheet.append([
        #     self.report_date.strftime('%Y-%m-%d'),
        #     self.get_stock_market_value(self.em_normal_position.positions, "600036"),
        #     self.get_credit_stock_market_value(self.em_credit_position.position_items, "600036"),
        #     self.get_stock_market_value(self.em_normal_position.positions, "600036") + self.get_credit_stock_market_value(self.em_credit_position.position_items, "600036"),
        #     "",
        #     self.get_stock_market_value(self.em_normal_position.positions, "510300"),
        #     "",
        #     # 比亚迪
        #     self.get_stock_market_value(self.em_normal_position.positions, "002594"),
        #     "",
        #     # 电池etf
        #     self.get_stock_market_value(self.em_normal_position.positions, "159755"),
        #     "",
        #     # 央企红利
        #     self.get_stock_market_value(self.em_normal_position.positions, "513910"),
        #     "",
        #     # 宁德时代
        #     self.get_stock_market_value(self.em_normal_position.positions, "300750"),
        #     self.get_credit_stock_market_value(self.em_credit_position.position_items, "300750"),
        #     self.get_stock_market_value(self.em_normal_position.positions,"300750") + self.get_credit_stock_market_value(self.em_credit_position.position_items, "300750"),
        #     "",
        #
        #     # 美的
        #     self.get_stock_market_value(self.em_normal_position.positions, "000333"),
        #     "",
        #     # 军工etf
        #     self.get_stock_market_value(self.em_normal_position.positions, "512660"),
        #     "",
        #     # 创新药
        #     self.get_stock_market_value(self.em_normal_position.positions, "515120"),
        #     "",
        #     # 正泰电器
        #     self.get_stock_market_value(self.em_normal_position.positions, "601877"),
        #     "",
        #     # 韦尔股份
        #     self.get_stock_market_value(self.em_normal_position.positions, "603501"),
        #     "",
        #     # 证券etf
        #     self.get_stock_market_value(self.em_normal_position.positions, "512880"),
        #     "",
        # ])

    def get_stock_market_value(self, positions: List, stock_code: str) -> float:
        """
        获取股票市值
        :param positions: 持仓列表
        :param stock_code: 股票代码
        :return: 股票市值
        """
        for position in positions:
            if position.stock_code == stock_code:
                return position.market_value
        return 0

    def get_credit_stock_market_value(self, positions: List, stock_code: str) -> float:
        """
        获取信用账户股票市值
        :param positions: 持仓列表
        :param stock_code: 股票代码
        :return: 股票市值
        """
        for position in positions:
            if position.stock_code == stock_code:
                return position.market_value
        return 0

    def create_em_summary_sheet(self, workbook: Workbook):

        sheet = workbook.create_sheet("东方财富小计")

        headers = [
            "当前日期", "普通账户总资产", "信用账户总资产", "期权账户总资产", "普通 + 信用", "合计",
            "普通账户证券市值", "信用账户证券市值", "期权账户保证金", "普通账户证券市值 + 信用账户证券市值", "合计",
            "0", "信用账户净资产", "净资产合计", "普通账户本周净流入", "信用账户本周净流入",
            "期权账户本周净流入", "本周净流入合计", "0", "0", "普通账户本周收益",
            "信用账户本周收益", "期权账户本周收益", "本周收益合计", "0", "0",
            "0", "0", "普通账户本年收益", "信用账户本年收益", "期权账户本年收益",
            "本年收益合计", "0", "普通账户累计收益", "信用账户累计收益", "期权账户累计收益",
            "累计收益合计", "0"
        ]

        sheet.append(headers)

        # 账户总资产
        em_normal_total = self.em_normal_position.total_assets
        em_credit_total = self.em_credit_position.total_assets
        em_option_total = self.ext_info.em_option_total_assets

        # 证券市值
        em_normal_stock_value = self.em_normal_position.market_value
        em_credit_stock_value = self.em_credit_position.total_market_value
        em_option_margin = self.ext_info.em_option_margin_balance

        data_row = [
            self.report_date.strftime("%Y-%m-%d"),
            em_normal_total,
            em_credit_total,
            em_option_total,
            # em_normal_total + em_credit_total,
            # em_normal_total + em_credit_total + em_option_total,
            0, 0,
            em_normal_stock_value,
            em_credit_stock_value,
            em_option_margin,
            # em_normal_stock_value + em_credit_stock_value,
            # em_normal_stock_value + em_credit_stock_value + em_option_margin,
            0, 0,
            0,  # 总负债
            self.em_credit_position.net_assets,
            # em_normal_total + self.em_credit_position.net_assets + em_option_total + 1050,
            0,
            self.ext_info.em_normal_weekly_net_inflow,
            self.ext_info.em_credit_weekly_net_inflow,
            self.ext_info.em_option_weekly_net_inflow,
            (self.ext_info.em_normal_weekly_net_inflow +
             self.ext_info.em_credit_weekly_net_inflow +
             self.ext_info.em_option_weekly_net_inflow),
            0,  # 期权总流入 todo
            0,  # 期初净资产
            self.ext_info.em_normal_weekly_profit,
            self.ext_info.em_credit_weekly_profit,
            self.ext_info.em_option_weekly_profit,
            # (self.ext_info.em_normal_weekly_profit +
            #  self.ext_info.em_credit_weekly_profit +
            #  self.ext_info.em_option_weekly_profit),
            0,
            0,  # 周涨幅 普通+信用 +期权
            0,  # 周涨幅 普通+信用
            0,  # 杠杆系数
            "",  # check
            self.ext_info.em_normal_yearly_profit,
            self.ext_info.em_credit_yearly_profit,
            0,  # 期权本年收益
            0,  # 本年收益合计 self.ext_info.em_normal_yearly_profit + self.ext_info.em_credit_yearly_profit,
            "",  # check
            self.ext_info.em_normal_total_profit,
            self.ext_info.em_credit_total_profit,
            0,  # 期权累计收益
            0, # 累计收益合计 self.ext_info.em_normal_total_profit + self.ext_info.em_credit_total_profit,
        ]

        sheet.append(data_row)

    def create_fund_summary_sheet(self, workbook: Workbook):
        """
        创建基金总结工作表
        :param workbook: Excel工作簿
        """
        sheet = workbook.create_sheet("基金小节")

        headers = [
            "当前日期", "合计资产金额", "合计累计收益", "合计本周收益", "0", "0",
            "合计本金收益", "蚂蚁基金资产", "蚂蚁基金累计收益", "0", "0",
            "且慢基金资产", "且慢基金累计收益", "0", "0",
            "十字星本周收益率"
            # "招商基金资产", "招商基金累计收益", "0", "0",
            # "天天基金资产", "天天基金累计收益", "0", "0",
        ]

        sheet.append(headers)

        # 基金数据
        alipay_assets = self.ext_info.alipay_fund_assets
        cmb_assets = self.ext_info.cmb_fund_assets
        tiantian_assets = self.ext_info.tiantian_fund_assets
        qieman_assets = self.ext_info.qieman_fund_assets

        alipay_profit = self.ext_info.alipay_fund_total_profit
        cmb_profit = self.ext_info.cmb_fund_total_profit
        tiantian_profit = self.ext_info.tiantian_fund_total_profit
        qieman_profit = self.ext_info.qieman_fund_total_profit

        # 合计
        total_assets = alipay_assets + cmb_assets + tiantian_assets + qieman_assets
        profit = alipay_profit + cmb_profit + tiantian_profit + qieman_profit
        profit = profit - Decimal(164804.31) + Decimal(60040.78)  # Adjustments as per Java code

        data_row = [
            self.report_date.strftime("%Y-%m-%d"),
            # total_assets,
            # profit,
            0, 0,
            0,  # 本周收益
            0, 0,  # Empty
            0,  # 本年收益
            alipay_assets,
            alipay_profit,
            0, 0,  # Empty
            qieman_assets,
            qieman_profit,
            0, 0,  # Empty

            self.ext_info.szx_rate,

            # cmb_assets,
            # cmb_profit,
            # 0, 0,  # Empty
            # tiantian_assets,
            # tiantian_profit,
            # 0, 0,  # Empty
        ]

        sheet.append(data_row)
    def create_baseline_sheet(self, workbook: Workbook):
        """
        创建基准页
        :param workbook: Excel工作簿
        """
        sheet = workbook.create_sheet("基准")
        headers = [
            "日期 ",
            "港股",
            "普通+信用+期权",
            "普通+信用",
            "基金",
            "上证50",
            "沪深300",
            "中证500",
            "中证1000",
            "国证2000",
            "北证50",
            "中证A50",
            "中证A500",
            "科创50",
            "科创100",
            "中证全指",
            "恒生",
            "恒科",
            "十字星"
        ]

        em_item_config = load_report_item_config("config/report_baseline_item_config.json")
        # 拉周线 key:item.name,value：item 最近一周涨幅
        weekly_change_dict = self.fetch_item_weekly_last_change(em_item_config)

        # 创建标题行
        header_row = ["当前日期","港股","普通+信用+期权","普通+信用","基金",]
        for item in em_item_config:
            name = item['name']
            header_row.append(f"{name}周涨幅")
        header_row.append("十字星")
        sheet.append(header_row)

        # 遍历获取每个证券的周线数据
        # 获取当前日期
        current_date = datetime.now()
        # 计算一个月前的日期
        one_month_ago = current_date - relativedelta(months=2)
        # 格式化为yyyyMMdd
        start_date = one_month_ago.strftime("%Y%m%d")
        end_date = current_date.strftime("%Y%m%d")

        # 创建数据行
        data_row = [self.report_date.strftime('%Y-%m-%d'),0,0,0,0]
        for item in em_item_config:
            name = item['name']
            ts_code = item['tsCode']
            item_type = item['itemType']
            print(f"\n正在获取{name}周线数据...")
            df = get_stock_weekly_data(item, start_date=start_date, end_date=end_date)
            if df is not None and not df.empty:
                last_change = df.sort_values('日期').iloc[-1]['涨跌幅']
                weekly_change_dict[name] = last_change
                print(f"{name}:{last_change}")
            else:
                print(f"{name}数据获取失败或返回空数据")
            # 添加涨跌幅数据
            data_row.append(weekly_change_dict.get(item['name'], ""))
        data_row.append(0)
        sheet.append(data_row)

    def create_debt_ratio_sheet(self, workbook: Workbook):
        """
        创建负债比率工作表
        :param workbook: Excel工作簿
        """
        # 具体实现可根据需求添加
        pass

def load_report_item_config(config_file):
    """从JSON配置文件加载证券列表"""
    if not os.path.exists(config_file):
        print(f"配置文件{config_file}不存在")
        return []

    try:
        with open(config_file, 'r', encoding='utf-8') as f:
            securities = json.load(f)
        print(f"成功从{config_file}加载{len(securities)}个证券")
        return securities
    except Exception as e:
        print(f"加载配置文件失败: {e}")
        return []


if __name__ == "__main__":
    generator = WeeklyReportExcelGenerator()
    generator.do_report(shift = 0)